options ls=120 symbolgen mlogic;   

/******************************************************************    
                                                                       
this pgm reads transunion non-roll-up data into sas datasets;
                                                                       
******************************************************************/    

libname save "/href/secure/student_loans/Out_data_sets/Transunion/2007/" ;   

%let sufx = 2007_june; 

*******************;
*** HEADER FILE ***;
*******************;

/*** MarketArea, SubmarketArea, In_File_SinceDate load only as character, this is different from Shira's code and also from previous data sets;***/

%macro header(num);

data head&num; 

     infile "/href/secure/student_loans/In_data_sets/Transunion_2007/NSAMP1/BLNKSUPP_07.txt" lrecl=2000 missover;
     input  		
		RecordType $ 1-2 
                MatchType $ 3-3 
		teditseq $ 4-15 
		In_File_SinceDate $ 16-21   
		MarketArea $ 22-23            
		SubmarketArea $	24-25            
		Accept_RejectCode $ 26-28 		
                CriteriaLevel $	29-31 	
		PermID2004 $ 37-49;
		
		if RecordType ne "HD" then delete;

proc sort data=head&num;
     by teditseq;
run;

%mend;

%header(1) ;

/*create dataset header2007_june*/
data save.header&sufx.;
	set head1;
run ;

/*proc print data=save.header&sufx. (obs=10);
run;
proc contents data=save.header&sufx;
run;

proc freq data=save.header&sufx;
     tables RecordType MatchType EditSequenceNumber In_File_SinceDate MarketArea SubmarketArea Accept_RejectCode CriteriaLevel PermID2004;
run;
*/
*******************;   
*** TRADE LINES ***;   
*******************;   

/*** DateOpened, DateVerified, DateReported, DateClosed, DatePaidOUt load as characters, should be numeric per Shira's code and per older data sets; 
*** Same issue for HighCredit, CurrentBalance, AmountPastDue, CurrentMOP, CreditLimit ;*/
/* There doesn't appear to be any data for variables maxdelvl, nummnths, past3059, past6089, past90up in the text file -jz 3/5/13*/
%macro trade(num);

data trade&num; 

     infile "/href/secure/student_loans/In_data_sets/Transunion_2007/NSAMP1/BLNKSUPP_07.txt" lrecl=2000 missover;

     input  		
     		RecordType $		1   - 2 @;
		if RecordType eq "TR" then 
		   input
		   MatchType		$	3   - 3 
		   teditseq		$	4   - 15				
		   subcdt		$  	17  - 24 
 		   dtopen		 	25 - 30 
		   ECOA 		$	31  - 31           
		   hicredit 		$	32  - 38   
       dtveri		 39 - 44
		   dtrept		 45 - 50 
		   dtclose 		 51 - 56      
		   dtpdout		 57 - 62 
		   assoccd		$	63  - 63           
		   PayPat 		$	64  - 111          
		   acctbal		$	112 - 118           
		   amtpast		$	119 - 125          
		   acctype 		$	126 - 126          
		   currmop		$	127 - 128        
		   crdtlim		$	129 - 135        
		   Terms 		$	136 - 143          
		   maxdeldt 	 144 - 149    
		   maxdelam		$	150 - 156    
		   maxdelvl		$	157 - 158          
		   nummnths		$ 	159 - 161          
		   past3059		$	162 - 163          
		   past6089		$	164 - 165          
		   past90up		$	166 - 167          
		   loantypt 		$	168 - 169          
		   dispcd		$	170 - 172          
		   collfld		$	173 - 205;
		else if RecordType ne "TR" then delete;  

		/*format dtopen dtveri dtrept dtclose dtpdout maxdeldt yymmn6.;*/
run;

proc sort data=trade&num;
     by teditseq;
run;

%mend;

%trade(1) ;

data save.trade&sufx.(rename=(HiCreditN = HiCredit acctbalN = acctbal amtpastN = amtpast crdtlimN = crdtlim maxdelamN = maxdelam));
     set trade1;

     /*set z's to missing*/
     if HiCredit eq "ZZZZZZZ" then HiCredit = .;
     if acctbal eq "ZZZZZZZ" then acctbal = .;
     if amtpast eq "ZZZZZZZ" then amtpast = .;
     if crdtlim eq "ZZZZZZZ" then crdtlim = .;
     if maxdelam eq "ZZZZZZZ" then maxdelam =.;
     if Terms eq "ZZZZZZZZ" then Terms = "";

     /*convert back to numbers*/
     format HiCreditN acctbalN amtpastN crdtlimN MaxDelAmN 7.0;

     HiCreditN = HiCredit*1;
     acctbalN = acctbal*1;
     amtpastN = amtpast*1;
     crdtlimN = crdtlim*1;
     maxdelamN = maxdelam*1;

     drop HiCredit acctbal amtpast crdtlim maxdelam;
run;

******************; 
*** INQUIRIES ***; 
******************; 

%macro iq(num);

data iq&num; 

     infile "/data/BLNKSUPP_07.txt" lrecl=2000 missover;
     input  		
     	RecordType $		1   - 2 @;
	if RecordType eq "IQ" then 
		   input
			MatchType		$	3 -	3 
			teditseq		$	4 -	15 
			InquiryTypeMask			16 -	16 
			SubscriberCode 		$	17 -	24 
			InquiryDate		??	yymmdd8.	/*25 -	32*/ 
									/*was commented out by kamila, dont know why*/
			ECOA			$	33 -	33 
			LoanType 		$	34 -	35 
			LoanAmount 		$	36 -	42;
	else if RecordType ne "IQ" then delete;

	format InquiryDate  yymmdd8.;
run;

proc sort data=iq&num;
	by teditseq;
run;

%mend;

%iq(1);

data save.inquiry&sufx.;
	set iq1;
run;
**********************; 
*** PUBLIC RECORDS ***;  
**********************; 

%macro pr(num);

data pr&num; 

infile "/href/secure/student_loans/In_data_sets/Transunion_2007/NSAMP1/BLNKSUPP_07.txt" lrecl=2000 missover;

       input  		
       			RecordType	$	1 -	2 @;
			if RecordType eq "PR" then 
		   	   input
				MatchType	$	3 -	3 
				teditseq 	$	4 -	15 
				DateReported	?? 	yymmn6.	
				Amount		$	22 -	27 
				PublicRecordType $	28 -	29 
				DatePaid	??	yymmn6.
				ECOA		$	36 -	36 
				Assets		$	37 -	42 
				Liabilities	$	43 -	48 
				Attorney	$	49 -	86 
				Plaintiff	$	87 -	124 
				DocketNumber	$	125 -	135 ;
			else if RecordType ne "PR" then delete;

			format DateReported DatePaid yymmn6.;
run;

proc sort data=pr&num;
	by teditseq;
run;


%mend;

%pr(1);

data save.pubrec&sufx.(rename=(AssetsN=Assets LiabilitiesN=Liabilities AmountN=Amount));
	set pr1;

     /*set z's to missing*/
     if Assets eq "ZZZZZZ" then Assets = .;
     if Liabilities eq "ZZZZZZ" then Liabilities = .;
     if Amount eq "ZZZZZZ" then Amount = .;
    
     /*convert back to numbers*/
     format AssetsN LiabilitiesN AmountN 6.0;

     AssetsN = Assets*1;
     LiabilitiesN = Liabilities*1;
     AmountN = Amount*1;

     drop Assets Liabilities Amount;
run;


proc contents data = save.pubrec&sufx.;
run;

*******************;
*** COLLECTIONS ***;
*******************;
%macro co(num);

data co&num;         

infile "/href/secure/student_loans/In_data_sets/Transunion_2007/NSAMP1/BLNKSUPP_07.txt" lrecl=2000 missover;

       input
		RecordType	$	1 -	2 @;
		if RecordType eq "CO" then 
		   	   input
				MatchType		$ 3 -	3 
				teditseq		$ 4 -	15 
				DateReported		?? yymmn6.	/*16 - 21*/
				SubscriberCode		$ 22 -	29 
				AmountOwed		  30 -	35
				Status			$ 36 -	37 
				DatePaid		?? yymmn6.	/*38 - 43*/
				ECOA			$ 44 -	44 
				CreditorName		$ 45 -	82 ;	
		else if RecordType ne "CO" then delete;

		format DateReported DatePaid yymmn6.;
run;

proc sort data=co&num;
	by teditseq;
run;

%mend;

%co(1);

data save.collect&sufx.;
	set co1;
run;

***************; 
*** SUMMARY ***; 
***************; 

%macro summary(num);

data sum&num; 

infile "/data/BLNKSUPP_07.txt" lrecl=2000 missover;

       input  		
       			RecordType	$	1 -	2 @;
			if RecordType eq "ZZ" then 
			input
				MatchType	$	3 -	3 
				teditseq	$	4 -	15 
				Inquiries		16 - 18 
				Trades			19 - 21  
				Collections		22 - 24 
				PublicRecords		25 - 27 
				MOP01			28 - 30 
				MOP02			31 - 33 
				MOP03			34 - 36 
				MOP04			37 - 39 
				MOP05			40 - 42 
				MOP07			43 - 45 
				MOP08			46 - 48 
				MOP09			49 - 51 
				MOP00			52 - 54 
				MOPUC			55 - 57 
				MOPUR			58 - 60 ;
			
			else if RecordType ne "ZZ" then delete;

proc sort data=sum&num;
	by teditseq;
run;

%mend;

%summary(1);

data save.summary&sufx.;
	set sum1;
if recordtype=" " then delete;
run;

proc contents data = save.summary&sufx.; 
run;

proc freq data = save.summary&sufx.;
     tables MatchType teditseq Inquiries Trades Collections PublicRecords MOP01 MOP02 MOP03 MOP04 MOP05 MOP07
     	    MOP08 MOP09 MOP00 MOPUC MOPUR;
run;

proc print data = save.summary&sufx. (obs = 10); 
run;

endsas ; 
